질의어와 SQL

질의어는 사용자가 데이터베이스에 존재하는 데이터를 요구하기 위해 사용하는 언어이다. 질의어의 종류는 다양하나 데이터베이스에서 가장 널리 이용되고 있는 질의어는 SQL이다. SQL은 표준 언어로서 거의 모든 DBMS에서 지원하고 있으며, 벤더별로 상이한 부분도 있지만 대부분의 관계형 데이터베이스에서 ANSI/ISO 표준을 최대한 따르고 있다.

SQL 문장의 3단계 처리과정

• Parse 단계: SQL문법 및 사용자의 보안/권한을 체크하며 가장 빠른 Execution Plan을 찾는다.
• Execute 단계: SQL문장을 실행한다.
• Fetch 단계: 실행 결과에 의한 결과 데이터를 전달한다.

DDL(Data Definition Language)

DDL은 데이터베이스에 저장될 수 있는 데이터들을 정의하는 언어이다. Oracle에서는 DDL 문장 수행 후 자동으로 커밋을 수행하는 반면 SQL Server는 자동 커밋을 수행하지 않는다.

CREATE

• SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의한다.
• 속성에 대한 제약조건은 CONSTRAINT를 이용하여 추가할 수 있다.

/* 일반 Create 구문 */
CREATE TABLE TEAM(
  TEAM_ID CHAR(3) NOT NULL,
  TEAM_NAME VARCHAR(40) NOT NULL,
  ORIG_YYYY CHAR(4),
  STADIUM_ID  CHAR(3) NOT NULL,
  ... -- 중략 --
  CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID),
  CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
);

/* CTAS: Create Table ~ as Select ~ 구문 이용시 */
CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;

ALTER

• 테이블에 대한 정의를 변경하는데 사용한다.
• MODIFY COLUMN 주의사항
  • 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. (데이터 훼손 방지)
  • 해당 칼럼이 NULL 값만 가지거나 혹은 테이블에 레코드가 없는 경우 칼럼의 크기를 줄일 수 있다.
  • 해당 칼럼이 NULL 값만 가지고 있으면 데이터유형을 변경할 수 있다.
  • 해당 칼럼에 NULL 값이 없을 경우만 NOT NULL 제약조건을 추가할 수 있다.

/* ADD COLUMN */
ALTER TABLE PLAYER
ADD (ADDRESS VARCHAR2(80));

/* ADD CONSTRAINT */
ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK
FOREIGN KEY (TEAM_ID) REFERENCES TEMA(TEAM_ID);

/* DROP COLUMN */
ALTER TABLE PLAYER
DROP COLUMN ADDRESS;

/* DROP CONSTRAINT */
ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_FK;

/* MODIFY COLUMN */
ALTER TABLE TEAM_TEMP
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '19860610' NOT NULL);

/* RENAME COLUMN */
ALTER TABLE PLAYER
RENAME COLUMN PLAYER_ID TO TEMP_ID;

DROP

• SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제한다.
• CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조제약조건의 삭제를 의미한다.

DROP TABLE PLAYER [CASCADE CONSTRAINT];

TRUNCATE

• 테이블은 유지되며 해당 테이블의 모든 레코드가 삭제된다. (정상적인 복구 불가)
• 기능이 DELETE와 유사하지만 자동 커밋 특성 등 내부처리방식이 다르며, DELETE보다는 TRUNCATE의 시스템 부하가 더 적다.

TRUNCATE TABLE TEAM;

RENAME

• 테이블의 이름을 변경할 수 있다.

RENAME TEAM TO TEAM_BACKUP;
RENAME TEAM_BACKUP TO TEAM;

DML(Data Manipulation Language)

DML은 테이블에 저장된 데이터들을 조작하는데 필요한 언어이다. Oracle은 DML 문장 수행 후 사용자가 임의로 커밋 혹은 롤백을 처리해야 트랜잭션이 종료된다. 반면 SQL Server는 기본적으로 자동 커밋 모드이기 때문에 사용자가 커밋이나 롤백을 처리할 필요가 없다.

INSERT

• 테이블에 새로운 레코드를 삽입한다.

INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('19920708', 'TOT', '손흥민', 'FW', 183, 77, 7);

SELECT

• 테이블에서 조건에 맞는 레코드를 검색한다.

SELECT * FROM PLAYER WHERE TEAM_ID = 'TOT';

/* 실행순서 5 */ SELECT   칼럼명 [ALIAS명]
/* 실행순서 1 */ FROM     테이블명
/* 실행순서 2 */ WHERE    조건식
/* 실행순서 3 */ GROUP BY 칼럼명, 표현식
/* 실행순서 4 */ HAVING   그룹조건식
/* 실행순서 6 */ ORDER BY 칼럼명, 표현식

UPDATE

• 테이블에서 조건에 맞는 레코드의 내용을 변경한다.

UPDATE PLYAER SET POSITION = 'ST' WHERE PLAYER_NAME = '손흥민';

DELETE

• 테이블에서 조건에 맞는 레코드를 삭제한다.

DELETE FROM PLAYER;

• DROP, TRUNCATE, DELETE 기능 비교

DCL(Data Control Language)

DCL은 사용자를 생성하고 권한을 제어할 수 있는 명령어이다.

GRANT

• 데이터베이스 사용자에게 사용권한을 부여한다.

/* GRANT STH TO USER */
GRANT CREATE USER TO SCOTT; -- SCOTT 사용자에게 유저생성 권한을 부여한다.
GRANT CREATE SESSION TO SHM; -- SHM 사용자가 로그인할 수 있도록 세션권한을 부여한다.
GRANT CREATE TABLE TO SHM; -- SHM 사용자에게 CREATE TABLE 권한을 부여한다.

REVOKE

• 데이터베이스 사용자의 사용권한을 취소한다.

/* REVOKE STH FROM USER */
REVOKE CREATE SESSION, CREATE TABLE FROM SHM;

ROLE

• 데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.
• ROLE에는 시스템 권한과 객체(OBJECT) 권한을 모두 부여할 수 있다.
• 권한을 직접 부여하는 것보다 ROLE을 사용하는 것이 빠르고 안전하게 사용자를 관리할 수 있는 방법이다.

CREATE ROLE TEMP_ROLE;
GRANT CREATE SESSION, CREATE TABLE TO TEMP_ROLE;
GRANT TEMP_ROLE TO SHM;

TCL(Transaction Control Language)

TCL은 DML에 의한 결과를 트랜잭션 별로 제어하는 명령어를 말한다. 일부에서는 DCL로 분류하기도 하지만, 성격이 다소 다르므로 별도 그룹으로 분리하는 것이 맞다.

COMMIT

• 반영된 데이터가 문제가 없다고 판단되는 경우 커밋 명령어를 통해 트랜잭션을 완료한다.

INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NM, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('19920708', 'TOT', '손흥민', 'FW', 183, 77, 7);
COMMIT;

ROLLBACK

• 커밋 수행 전인 경우 롤백 명령어로 트랜잭션 시작 이전의 상태로 데이터를 되돌린다.

UPDATE PLAYER SET HEIGHT = 173 WHERE PLAYER_ID = '19920708';
ROLLBACK;

SAVEPOINT

• 롤백에 저장점 명을 부여하여 실행하면, 저장점 설정 이후에 있었던 데이터 변경에 대해서만 원래 데이터 상태로 되돌린다.

SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;